﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrmMain
{
  public  class Dao
    {
        private const string constr = "Data Source=.;Initial Catalog=KTV;Integrated Security=True";
        private SqlConnection GetCon()
        {
            return new SqlConnection(constr);
        }
        private SqlConnection GetOpenCon()
        {
            SqlConnection con = GetCon();
            con.Open();
            return con;
        }

        public int DoNonquery(string sql,SqlParameter[] parame)
        {
            using (SqlConnection con = GetOpenCon())
            {
                SqlCommand com = new SqlCommand(sql, con);
                if (parame != null) {
                    com.Parameters.AddRange(parame);
                }
               
                return com.ExecuteNonQuery();
            }
        }
        public object DoScalar(string sql)
        {
            using (SqlConnection con = GetOpenCon())
            {
                SqlCommand com = new SqlCommand(sql, con);
                return com.ExecuteScalar();
            }
        }
        public object DoScalar(string sql, SqlParameter[] parame)
        {
            using (SqlConnection con = GetOpenCon())
            {
                SqlCommand com = new SqlCommand(sql, con);
                if (parame != null) {
                    foreach (SqlParameter item in parame)
                    {
                        com.Parameters.Add(item);
                    }
                }
               
                return com.ExecuteScalar();
            }
        }

        public List<Dictionary<string, object>> DoDataReader(string[] cloumns, string sql, SqlParameter[] param)
        {
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            //获得打开的数据库连接
            using (SqlConnection conn = this.GetOpenCon())
            {
                //获得command对象
                SqlCommand command = new SqlCommand(sql, conn);
                //使用命令化参数防止sql注入
                command.Parameters.AddRange(param);
                //获得datareader对象
                //使用CommandBehavior.CloseConnection 让系统资管关闭reader对象
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                //使用datareader对象去去读取数据 存入dic字典中
                while (reader.Read())
                {
                    //我们要根据我们传递过来的列名 来获取数据 存入字典中
                    Dictionary<string, object> dic = new Dictionary<string, object>();
                    foreach (string item in cloumns)
                    {
                        dic.Add(item, reader[item]);
                    }
                    //将字典添加到集合中
                    list.Add(dic);
                }
                //返回结果
                return list;
            }
        }
        public DataTable GetTable(string sql, SqlParameter[] parame)
        {
            //1获得未打开的数据库连接对象
            using (SqlConnection conn = this.GetCon())
            {
                //创建适配器对象
                SqlDataAdapter adapter = new SqlDataAdapter();
                //创建command对象
                SqlCommand command = new SqlCommand(sql, conn);
                //添加命令化参数 防止sql注入
                if (parame != null)
                {
                    command.Parameters.AddRange(parame);
                }
                //给适配器的查询执行命令对象赋值
                adapter.SelectCommand = command;
                //填充datatable
                DataTable table = new DataTable();
                //填充数据表
                adapter.Fill(table);
                //返回datatable
                return table;
            }
            //2创建dataadapter适配器
            //3填充datatble
            //4返回datatable
        
    }
    }
}
